﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Common;
using MSL.DataBase.Lib;
using System.Collections;
using MSL.DataBase.SQL;
using System.IO;

namespace MSL.DataBase.Core
{
    internal class MySqlHelper : DbBase
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">链接字符串</param>
        public MySqlHelper(string conn)
            : base(DataBaseType.MySql, conn, DBConfig.MySQL)
        {

        }
        public MySqlHelper()
            : base(DataBaseType.MySql, DBConfig.ConnString, DBConfig.MySQL)
        {

        }
        /// <summary>
        /// 
        /// </summary>
        public override void AddReturnPara()
        {

        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override System.Data.Common.DbParameter GetNewParameter()
        {
            return new MySqlParameter();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="dataType"></param>
        /// <param name="size"></param>
        /// <param name="direction"></param>
        public override void AddParameters(string parameterName, object value, DbType dataType, int size, ParameterDirection direction)
        {
            parameterName = parameterName.Substring(0, 1) == "@" ? parameterName : "@" + parameterName;
            MySqlParameter para = new MySqlParameter();
            para.ParameterName = parameterName;
            para.Value = value;
            if (dataType == DbType.DateTime)
            {
                para.MySqlDbType = MySqlDbType.DateTime;
                para.Value = Convert.ToString(value);
            }
            else
            {
                para.DbType = dataType;
                para.Value = value;
            }
            Com.Parameters.Add(para);
        }

        #region 分页查询

        public override DataTable Query(string tableName, string primaryKey, string showFields, int pageIndex, int pageSize, string whereStr, string sortBy, string groupBy, out int recordCount)
        {
            recordCount = 0;
            DataTable dt = null;
            Hashtable parameters = new Hashtable();

            string sql = SQLString.MySqlPage(pageIndex, pageSize, whereStr, tableName, showFields, sortBy, recordCount, true);
            recordCount = Convert.ToInt32(ExecScalar(sql, false));
            if (recordCount > 0 && (pageIndex - 1) * pageSize < recordCount)
            {
                sql = SQLString.MySqlPage(pageIndex, pageSize, whereStr, tableName, showFields, sortBy, recordCount, false);
                dt = Query(sql, false);
            }
            base._com.Parameters.Clear();
            return dt;
        }

        #endregion

        #region 批量操作

        /// <summary>
        /// 大批量数据插入
        /// </summary>
        /// <param name="table">数据表</param>
        public override void BulkInsert(DataTable table)
        {
            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");

            string tmpPath = Path.GetTempFileName();
            StringBuilder csv = DataTableToCSV(table);
            File.WriteAllText(tmpPath, csv.ToString());
            using (MySqlConnection conn = new MySqlConnection(base._com.Connection.ConnectionString))
            {
                try
                {
                    base.Open();
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                    {
                        FieldTerminator = ",",
                        FieldQuotationCharacter = '"',
                        EscapeCharacter = '"',
                        LineTerminator = "\r\n",
                        FileName = tmpPath,
                        NumberOfLinesToSkip = 0,
                        TableName = table.TableName,
                    };
                    bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                    //insertCount = bulk.Load();
                }
                catch (MySqlException ex)
                {
                    base.RollbackTransaction();
                }
                finally
                {
                    Close();
                }
            }
            File.Delete(tmpPath);
            //return insertCount;
        }
        /// <summary>
        /// 导出报表为Csv
        /// </summary>
        /// <param name="dt">DataTable</param>m>
        private StringBuilder DataTableToCSV(DataTable dataTable)
        {
            StringBuilder StringBuilder = new StringBuilder();
            // 写出表头
            foreach (DataColumn DataColumn in dataTable.Columns)
            {
                StringBuilder.Append(DataColumn.ColumnName.ToString() + ",");
            }
            StringBuilder.Append("\n");
            // 写出数据
            foreach (DataRowView dataRowView in dataTable.DefaultView)
            {
                foreach (DataColumn DataColumn in dataTable.Columns)
                {
                    StringBuilder.Append(dataRowView[DataColumn.ColumnName].ToString() + ",");
                }
                StringBuilder.Append("\n");
            }
            return StringBuilder;
        }
        #endregion 批量操作

        #region 数据库管理

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backfile">备份文件</param>
        /// <returns>还原成功与否提示</returns>
        public override bool RestoreDatabase(string dbName, string backfile)
        {
            return false;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="dbFileName">备份文件</param>
        /// <returns>是否备份成功</returns>
        public override bool BackDataBase(string dbName, string dbFileName)
        {
            return false;
        }

        /// <summary>
        /// 新建数据库
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        public override bool CreateDataBase(string databaseName)
        {
            return false;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        public override bool DropDataBase(string databaseName)
        {
            return false;
        }
        #endregion
    }
}
